package com.moinut.studentattendancemanager.model.dao.impl;

import com.moinut.studentattendancemanager.model.dao.IAttendanceInfoDAO;
import com.moinut.studentattendancemanager.model.vo.AttendanceInfo;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author MOILING
 */
public class AttendanceInfoDAOImpl extends BaseDAOImpl implements IAttendanceInfoDAO {

    public AttendanceInfoDAOImpl(Connection connection) {
        super(connection);
    }

    @Override
    public List<AttendanceInfo> getAttendancesInfoByCourseId(String courseId) throws Exception {
        List<AttendanceInfo> attendancesInfo = new ArrayList<>();
        String sql = "SELECT student_name, record_time, course_name, attendance_status, class_time " +
                     "FROM attendancerecord, attendanceinfo, classinfo, studentinfo " +
                     "WHERE attendancerecord.attendance_id IN (" +
                         "SELECT attendanceinfo.attendance_id " +
                         "FROM attendanceinfo " +
                         "WHERE attendanceinfo.class_id IN (" +
                             "SELECT class.class_id " +
                             "FROM classinfo AS class " +
                             "WHERE class.course_id = ?" +
                         ")" +
                     ") " +
                     "AND attendancerecord.attendance_id = attendanceinfo.attendance_id " +
                     "AND classinfo.class_id = attendanceinfo.class_id " +
                     "AND studentinfo.student_id = attendancerecord.student_id";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, courseId);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            AttendanceInfo attendanceInfo = new AttendanceInfo(resultSet.getString(2),
                    resultSet.getString(3), resultSet.getString(4), resultSet.getString(1));
            attendanceInfo.setClassTime(resultSet.getString(5));
            attendancesInfo.add(attendanceInfo);
        }
        if (this.preparedStatement != null) {
            this.preparedStatement.close();
        }
        return attendancesInfo;
    }

    @Override
    public List<AttendanceInfo> getAttendancesInfoByCourseIdAndStudentId(String courseId, String studentId) throws Exception {
        List<AttendanceInfo> attendancesInfo = new ArrayList<>();
        String sql = "SELECT student_name, record_time, course_name, attendance_status, class_time " +
                     "FROM attendancerecord, attendanceinfo, classinfo, studentinfo " +
                     "WHERE attendancerecord.attendance_id IN (" +
                         "SELECT attendanceinfo.attendance_id " +
                         "FROM attendanceinfo " +
                         "WHERE attendanceinfo.class_id IN (" +
                             "SELECT class.class_id " +
                             "FROM classinfo AS class " +
                             "WHERE class.course_id = ?" +
                         ")" +
                     ") " +
                     "AND attendancerecord.attendance_id = attendanceinfo.attendance_id " +
                     "AND classinfo.class_id = attendanceinfo.class_id " +
                     "AND studentinfo.student_id = attendancerecord.student_id " +
                     "AND studentinfo.student_id = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, courseId);
        preparedStatement.setString(2, studentId);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            AttendanceInfo attendanceInfo = new AttendanceInfo(resultSet.getString(2),
                    resultSet.getString(3), resultSet.getString(4), resultSet.getString(1));
            attendanceInfo.setClassTime(resultSet.getString(5));
            attendancesInfo.add(attendanceInfo);
        }
        if (this.preparedStatement != null) {
            this.preparedStatement.close();
        }
        return attendancesInfo;
    }

    @Override
    public List<AttendanceInfo> getAllAttendancesInfo() throws Exception {
        List<AttendanceInfo> attendancesInfo = new ArrayList<>();
        String sql = "SELECT classinfo.class_id, course_name, studentinfo.student_id, student_name, record_time, attendance_status " +
                     "FROM attendancerecord, attendanceinfo, classinfo, studentinfo " +
                     "WHERE attendancerecord.attendance_id IN (" +
                         "SELECT attendanceinfo.attendance_id " +
                         "FROM attendanceinfo " +
                     ") " +
                     "AND attendancerecord.attendance_id = attendanceinfo.attendance_id " +
                     "AND classinfo.class_id = attendanceinfo.class_id " +
                     "AND studentinfo.student_id = attendancerecord.student_id ";
        preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            AttendanceInfo attendanceInfo = new AttendanceInfo(resultSet.getString(1), resultSet.getString(2),
                    resultSet.getString(3), resultSet.getString(4), resultSet.getString(5), resultSet.getString(6));
            attendancesInfo.add(attendanceInfo);
        }
        if (this.preparedStatement != null) {
            this.preparedStatement.close();
        }
        return attendancesInfo;
    }

    @Override
    public List<AttendanceInfo> getAttendancesInfoByTeacherId(String teacherId) throws Exception {
        List<AttendanceInfo> attendancesInfo = new ArrayList<>();
        String sql = "SELECT classinfo.class_id, course_name, studentinfo.student_id, student_name, record_time, attendance_status " +
                     "FROM attendancerecord, attendanceinfo, classinfo, studentinfo " +
                     "WHERE attendancerecord.attendance_id IN (" +
                         "SELECT attendanceinfo.attendance_id " +
                         "FROM attendanceinfo " +
                         ") " +
                     "AND attendancerecord.attendance_id = attendanceinfo.attendance_id " +
                     "AND classinfo.class_id = attendanceinfo.class_id " +
                     "AND studentinfo.student_id = attendancerecord.student_id " +
                     "AND classinfo.teacher_id = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, teacherId);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            AttendanceInfo attendanceInfo = new AttendanceInfo(resultSet.getString(1), resultSet.getString(2),
                    resultSet.getString(3), resultSet.getString(4), resultSet.getString(5), resultSet.getString(6));
            attendancesInfo.add(attendanceInfo);
        }
        if (this.preparedStatement != null) {
            this.preparedStatement.close();
        }
        return attendancesInfo;
    }

    @Override
    public boolean updateAttendanceInfo(AttendanceInfo attendanceInfo) throws Exception {
        String sql = "UPDATE attendancerecord SET attendance_status = ? WHERE attendance_id IN (" +
                         "SELECT attendance_id " +
                         "FROM attendanceinfo " +
                         "WHERE class_id = ? " +
                         "AND record_time = ?" +
                     ") " +
                     "AND attendancerecord.student_id = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, attendanceInfo.getState());
        preparedStatement.setString(2, attendanceInfo.getCourseId());
        preparedStatement.setString(3, attendanceInfo.getTime());
        preparedStatement.setString(4, attendanceInfo.getStudentId());
        int updateNumber = preparedStatement.executeUpdate();
        if (updateNumber > 0) {
            return true;
        }
        if (this.preparedStatement != null) {
            this.preparedStatement.close();
        }
        return false;
    }

    @Override
    public boolean insertAttendanceInfo(AttendanceInfo attendanceInfo) throws Exception {
        String insertInfo = "INSERT INTO attendanceinfo(class_id, record_time) VALUES (?, ?)";
        String query = "SELECT attendance_id FROM attendanceinfo WHERE class_id = ? AND record_time = ?";
        String insertCord = "INSERT INTO attendancerecord(attendance_id, student_id, attendance_status) VALUES (?, ?, ?)";
        int returnedVal;
        preparedStatement = connection.prepareStatement(insertInfo);
        preparedStatement.setString(1, attendanceInfo.getClassId());
        preparedStatement.setString(2, attendanceInfo.getTime());
        returnedVal = preparedStatement.executeUpdate();
        ResultSet resultSet;
        if (returnedVal > 0) {
            preparedStatement = connection.prepareStatement(query);
            preparedStatement.setString(1, attendanceInfo.getClassId());
            preparedStatement.setString(2, attendanceInfo.getTime());
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                preparedStatement = connection.prepareStatement(insertCord);
                preparedStatement.setString(1, resultSet.getString(1));
                preparedStatement.setString(2, attendanceInfo.getStudentId());
                preparedStatement.setString(3, attendanceInfo.getState());
                returnedVal = preparedStatement.executeUpdate();
            }
        }
        if (this.preparedStatement != null) {
            this.preparedStatement.close();
        }
        return returnedVal > 0;
    }
}
